﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data;
using System.Windows.Forms;

namespace UniMana.Data
{
    public static class Training_Student_Subject_ClassData
    {
        /// <summary>
        /// Lấy danh sách sinh viên của một lớp học phần
        /// </summary>
        /// <param name="t1">Table Training_Student_Subject_Class</param>
        /// <param name="t2">Table Training_Student_Class</param>
        /// <param name="t3">Table Training_Student</param>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        /// <returns></returns>
        public static IQueryable GetStudentsSubjectClass(this Table<Training_Student_Subject_Class> t1, 
                    Table<Training_Student_Class> t2,Table<Training_Student> t3, int subjectClassKey)
        {
            //DataTable table = new DataTable();
            var students = from studentClass in t2.Where(p=>p.Mainly==true)
                           join
                                 student in t3 on studentClass.Training_StudentKey equals student.Training_StudentKey
                           join
                                 studentSubjectClass in t1 on student.Training_StudentKey equals studentSubjectClass.Training_StudentKey
                           where studentSubjectClass.Training_Subject_ClassKey == subjectClassKey
                           where studentClass.Training_TrainingProgramKey == studentSubjectClass.Training_TrainingProgramKey
                           orderby studentClass.ID 
                           //orderby st.SureName
                           select new
                           {
                               student.Training_StudentKey,
                               studentClass.ID,
                               student.LastName,
                               student.SureName,
                               Birthday = student.Birthday == null ? student.ExceptionBirthday.ToString(): student.Birthday.ToString() ,
                               Gender = student.Gender == true ? "Nữ":"Nam",
                           };
            return students;
            //table.Columns.Add("Training_StudentKey");
            //table.Columns.Add("ID");
            //table.Columns.Add("LastName");
            //table.Columns.Add("SureName");
            //table.Columns.Add("Birthday");
            //table.Columns.Add("Gender");
            //foreach (var result in students)
            //{
            //    table.Rows.Add(new object[] {result.Training_StudentKey, result.ID, result.LastName, result.SureName, result.Birthday, result.Gender });
            //}
            //return table;
        }
        /// <summary>
        /// Lay diem trung binh cuoi cung cua sinh vien thuoc lop hoc phan
        /// </summary>
        /// <param name="t1"></param>
        /// <param name="subjectClassKey"></param>
        /// <returns></returns>
        public static IQueryable GetList(this Table<Training_Student_Subject_Class> t1, 
            Table<Training_Student> t2, Table<Training_Student_Class> t3, int subjectClassKey)
        {
            var s = from l in t1 join 
                         st in t2 on l.Training_StudentKey equals st.Training_StudentKey
                         join stc in t3 on l.Training_StudentKey equals stc.Training_StudentKey
                    //orderby st.SureName
                    orderby stc.ID
                    where l.Training_Subject_ClassKey == subjectClassKey
                    where stc.Mainly ==true
                    select l;
            return s;
        }

        #region [Chi]
        public static void DeleteByClassKey(
        this Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
        int _iTraining_Subject_ClassKey)
        {
            DBMain _dbTraining = new DBMain();
            var iQuery = from studentSubjectClass in _dbTraining.m_DCSave.Training_Student_Subject_Classes
                         where studentSubjectClass.Training_Subject_ClassKey == _iTraining_Subject_ClassKey
                         select studentSubjectClass;

            _dbTraining.m_DCSave.Training_Student_Subject_Classes.DeleteAllOnSubmit(iQuery);
            _dbTraining.m_DCSave.SubmitChanges();
        }
        #endregion

        #region [An]
        /// <summary>
        /// Trả về các lớp đầu vào với mã lớp học phần
        /// </summary>
        /// <param name="nTraining_Student_Subject_Class"></param>
        /// <param name="nTraining_Student_Class"></param>
        /// <param name="nTraining_Class"></param>
        /// <param name="m_iTrainingSubjectClassKey"></param>
        /// <returns></returns>

        public static IQueryable ReturnTrainingClassIDWithTrainingSubjectClassKey(this Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Class> nTraining_Class,
            int m_iTrainingSubjectClassKey)
        {
            var values = (from t in nTraining_Student_Subject_Class
                          join t0 in nTraining_Student_Class
                                on new { t.Training_StudentKey, t.Training_TrainingProgramKey }
                            equals new { t0.Training_StudentKey, Training_TrainingProgramKey = Convert.ToInt32(t0.Training_TrainingProgramKey) }
                          join t1 in nTraining_Class on new { Training_ClassKey = Convert.ToInt32(t0.Training_ClassKey) } equals new { Training_ClassKey = t1.Training_ClassKey }
                          where
                            t.Training_Subject_ClassKey == m_iTrainingSubjectClassKey
                          select new
                          {
                              t1.ID
                          }).Distinct();
            return values;
        }
        #endregion
    }
}
